package cn.xiaou.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.xiaou.dao.UserDao;
import cn.xiaou.domain.ModifyPwd;
import cn.xiaou.domain.Stock;
import cn.xiaou.domain.Transaction;
import cn.xiaou.domain.User;
import cn.xiaou.domain.UserReal;
import cn.xiaou.utils.DataSourceUtils;

public class UserDaoImpl implements UserDao {

	@Override
	public Integer registerUser(User user) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "INSERT INTO  users  VALUES (null,?,?,?,?,?,?,?,?,?,?)";
		return qr.update(sql, user.getUsername(),user.getPassword(),user.getHobby(),user.getGender(),
								user.getEducation(),user.getEmail(),user.getEmail_state(),
								user.getSynopsis(),user.getBirthday(),user.getMoney());
	}

	@Override
	public Long isExistUserByUsername(String username) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from users where username=?";
		return qr.query(sql, new ScalarHandler<>(), username);
	}

	@Override
	public Long checkExistEmail(String email) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from users where email=?";
		return qr.query(sql, new ScalarHandler<>(), email);
	}

	@Override
	public User loginUser(User user) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from users where username=? and password =?";
		return qr.query(sql, new BeanHandler<User>(User.class), user.getUsername(),user.getPassword());
	}

	@Override
	public void activationMailbox(Integer uid) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update users set email_state =0 where uid =?";
		qr.update(sql, uid);
	}

	@Override
	public User userByUsername(String username) throws SQLException {
		QueryRunner qr = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select uid,email from users where username=?";
		return qr.query(sql, new BeanHandler<User>(User.class),username);
	}

	@Override
	public User userByEmail(String email) throws SQLException {
		QueryRunner qr = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select uid,email from users where email=?";
		return qr.query(sql, new BeanHandler<User>(User.class),email);
	}

	@Override
	public Integer addModifyPwd(ModifyPwd modify) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "INSERT INTO  modify_pwd  VALUES (null,?,?,?,?)";
		qr.update(sql,modify.getModify_code(),modify.getModify_date(),modify.getModify_state(),modify.getUser().getUid());
		sql = "select modify_id from modify_pwd where uid=? and modify_state=1";
		return qr.query(sql, new BeanHandler<ModifyPwd>(ModifyPwd.class),modify.getUser().getUid() ).getModify_id();
	}

	@Override
	public Long checkModifyPwdBycode(String codeStr, Integer modify_id) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from modify_pwd where modify_id=? and modify_state=1 and modify_code=?";
		return qr.query(sql, new ScalarHandler<>(),modify_id, codeStr);
	}

	@Override
	public void updateModifyPwdState(Integer modify_id) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql ="update modify_pwd set modify_state =0 where modify_id=?";
		qr.update(sql,modify_id);
		
	}

	@Override
	public User findUidByModify_id(Integer modify_id) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql ="select uid from modify_pwd  where modify_id=?";
		return qr.query(sql, new BeanHandler<User>(User.class),modify_id);
	}

	@Override
	public Integer updatePassword(User user) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update users set password=? where uid=?";
		return qr.update(sql,user.getPassword(),user.getUid());
	}


	@Override
	public long checkModifyPwding(User user) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from modify_pwd where modify_state=1 and uid=?";
		return qr.query(sql, new ScalarHandler<>(),user.getUid());
	}

	@Override
	public long checkModifyCount(User user, String now) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from modify_pwd where modify_date >? and uid=?";
		return qr.query(sql, new ScalarHandler<>(),now,user.getUid());
	}

	@Override
	public Integer addRealName(UserReal userReal) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "insert into user_real values(?,?,?,?)";
		return qr.update(sql,null,userReal.getRealname(),userReal.getId_card(),userReal.getUser().getUid());
	}

	@Override
	public UserReal checkRealnameExist(User user) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from user_real where uid =?";
		return qr.query(sql, new BeanHandler<UserReal>(UserReal.class),user.getUid());
	}

	@Override
	public List<Stock> findAllStock() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		return qr.query("select * from stock", new BeanListHandler<Stock>(Stock.class));
	}

	@Override
	public void addOrder(Transaction tran) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "insert into transaction values(?,?,?,?,?)";
		qr.update(DataSourceUtils.getConnection(),sql,null,tran.getUser().getUid(),tran.getStock().getSid(),tran.getValue(),tran.getTotal());
	}

	@Override
	public void pay(Transaction tran) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "update users set money = money -? where uid =?";
		qr.update(DataSourceUtils.getConnection(), sql,tran.getTotal(),tran.getUser().getUid());
	}

	@Override
	public void updateStockCount(Transaction tran) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql= "update stock set snew_price = snew_price - ? where sid =?";
		qr.update(DataSourceUtils.getConnection(), sql, tran.getValue(),tran.getStock().getSid());
		
	}

	@Override
	public Integer Recharge(User user, Integer money) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="update users set money = money + ? where uid =? and username = ? and password =?";
		return qr.update(sql, money,user.getUid(),user.getUsername(),user.getPassword());
	}

	@Override
	public List<Transaction> userTransaction(User user) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from transaction where uid = ?";
		return qr.query(sql, new BeanListHandler<Transaction>(Transaction.class),user.getUid());
	}

	@Override
	public Stock findTransactionBysid(Integer sid) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from stock where sid = ?";
		return qr.query(sql, new BeanHandler<Stock>(Stock.class),sid);
	}

	@Override
	public void addMoney(Integer uid, Double money) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "update users set money = money +? where uid =?";
		qr.update(DataSourceUtils.getConnection(),sql, money,uid);
	}

	@Override
	public void updateTransaction(Transaction tran) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "update transaction set value = ?,total=? where tid =?";
		qr.update(DataSourceUtils.getConnection(),sql, tran.getValue(),tran.getTotal(),tran.getTid());
		
	}

	@Override
	public Integer checkOrderZero(Integer tid) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "select value from transaction where tid = ?";
		 List<Integer> query = qr.query(DataSourceUtils.getConnection(),sql, new ColumnListHandler<>("value"),tid);
		 for (Integer integer : query) {
			return integer;
		}
		return 0;
		 
	}

	@Override
	public void deleteTransaction(Transaction tran) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "delete from transaction where tid = ?";
		qr.update(DataSourceUtils.getConnection(),sql,tran.getTid());
	}

	@Override
	public void updateCount(Transaction tran,Integer count) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "UPDATE stock SET snew_price = snew_price + ? WHERE scode = ?";
		qr.update(DataSourceUtils.getConnection(),sql,count,tran.getSid());
	}

	@Override
	public Integer findDistinct(Transaction tran) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "select tid from transaction where uid=? and sid=?";
		List<Integer> list = qr.query(DataSourceUtils.getConnection(), sql, new ColumnListHandler<Integer>(), tran.getUser().getUid(),tran.getStock().getSid());
		for (Integer i : list) {
			return i;
		}
		return 0;
	}

	@Override
	public void updateOrder(Transaction tran, Integer state) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "update transaction set value = value+?,total = total + ? where tid =?";
		qr.update(DataSourceUtils.getConnection(), sql, tran.getValue(),tran.getTotal(),state);
	}


}
